﻿using System;
using System.Data.Common;
using Avanzis.QueryBuilder.Fluent;
using Avanzis.QueryFramework;
using NUnit.Framework;

namespace Avanzis.QueryBuilder.NUnit.Fluent
{
    [TestFixture]
    public class SelectTests : SelectBuilderTest
    {
        private Dialects previousDialect = Dialects.MSSql2005Dialect;

        [TestFixtureSetUp]
        public virtual void Setup()
        {
            previousDialect = QueryFrameworkConfiguration.Dialect;
            QueryFrameworkConfiguration.Dialect = Dialects.MSSql2000Dialect;
            QueryProcessor.ResetProcessor();
        }


        [TestFixtureTearDown]
        public virtual void TearDown()
        {
            QueryFrameworkConfiguration.Dialect = previousDialect;
        }

        [Test]
        public override void SimpleLimit10()
        {
            Select sb = new Select()
                .Field("ArticleId")
                .Field("Name")
                .From("Articles")
                .Take(10);


            string result = "SELECT TOP 10 ArticleId,Name\n" +
                            "FROM [Articles]\n";

            Assert.AreEqual(result, sb.ToString());
            Select cloned = (Select) sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        [ExpectedException(typeof (InvalidOperationException))]
        public override void SimpleOffset10()
        {
            QueryFrameworkConfiguration.Dialect = Dialects.MSSql2000Dialect;

            Select sb = new Select()
                .Field("ArticleId")
                .Field("Name")
                .From("Articles")
                .Offset(10);

            string result = "SELECT ArticleId,Name\n" +
                            "FROM [Articles]\n";

            Assert.AreEqual(result, sb.ToString());
            Select cloned = (Select) sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        [ExpectedException(typeof (InvalidOperationException))]
        public override void SimpleLimit20Offset10()
        {
            QueryFrameworkConfiguration.Dialect = Dialects.MSSql2000Dialect;

            Select sb = new Select()
                .Field("ArticleId")
                .Field("Name")
                .From("Articles")
                .Take(20)
                .Offset(10);


            string result = "SELECT TOP 20 ArticleId,Name\n" +
                            "FROM [Articles]\n";

            Assert.AreEqual(result, sb.ToString());
            Select cloned = (Select) sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        [ExpectedException(typeof (InvalidOperationException))]
        public override void SimpleLimit20Offset10WithOrderBy()
        {
            QueryFrameworkConfiguration.Dialect = Dialects.MSSql2000Dialect;

            Select sb = new Select()
                .Field("ArticleId")
                .Field("Name")
                .Take(20)
                .Offset(10)
                .OrderBy("Name");

            string result = "SELECT ArticleId,Name\n" +
                            "FROM [Articles]\n" +
                            "ORDER BY Name\n" +
                            "LIMIT 20\n" +
                            "OFFSET 10\n";

            Assert.AreEqual(result, sb.ToString());
            Select cloned = (Select) sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        public override void SingleJoinWithMultipleConditions()
        {
            Select sb = new Select()
                .Field("Articles.ArticleId")
                .Field("Articles.Name")
                .Field("Providers.Name")
                .From("Articles")
                .Join(JoinTypes.Left, "Providers", new Filters()
                                                       .And("Articles.ProviderId = Providers.ProviderId")
                                                       .And("Articles.ArticleType = Providers.ArticleType"));

            string result = "SELECT Articles.ArticleId,Articles.Name,Providers.Name\n" +
                            "FROM [Articles] " +
                            "LEFT JOIN [Providers] ON (((Articles.ProviderId = Providers.ProviderId) AND (Articles.ArticleType = Providers.ArticleType)))\n";

            Assert.AreEqual(result, sb.ToString());
            Select cloned = (Select) sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        public override void ReuseFieldsInWhereAndGroupBy()
        {
            SqlField field1 = new SqlField("Articles.ArticleId");
            SqlField field2 = new SqlField("AVG(UnitCost)", "Media Unit Cost");

            Select sb = new Select()
                .Field(field1)
                .Field(field2)
                .From("Articles")
                .Where(field1, SqlOperator.Equals, 92)
                .GroupBy(field1);

            string result = "SELECT Articles.ArticleId,AVG(UnitCost) AS 'Media Unit Cost'\n" +
                            "FROM [Articles]\n" +
                            "WHERE (Articles.ArticleId = 92)\n" +
                            "GROUP BY Articles.ArticleId\n";

            Assert.AreEqual(result, sb.ToString());
            Select cloned = (Select) sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        public override void MultipleJoins()
        {
            Select sb = new Select()
                .Field("Articles.ArticleId")
                .Field("Articles.Name")
                .Field("ArticleFamilies.Name")
                .Field("Providers.Name")
                .From("Articles")
                .Join("ArticleFamilies", "Articles.ArticleFamilyId", SqlOperator.Equals, "ArticleFamilies.ArticleFamilyId")
                .LeftJoin("Providers", "Articles.ProviderId = Providers.ProviderId");

            string result = "SELECT Articles.ArticleId,Articles.Name,ArticleFamilies.Name,Providers.Name\n" +
                            "FROM [Articles] " +
                            "INNER JOIN [ArticleFamilies] ON ((Articles.ArticleFamilyId = ArticleFamilies.ArticleFamilyId)) " +
                            "LEFT JOIN [Providers] ON ((Articles.ProviderId = Providers.ProviderId))\n";

            Assert.AreEqual(result, sb.ToString());
            Select cloned = (Select) sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        public override void SimpleJoin()
        {
            Select sb = new Select()
                .Field("Articles.ArticleId")
                .Field("Articles.Name")
                .Field("ArticleFamilies.Name")
                .From("Articles")
                .Join("ArticleFamilies", "Articles.ArticleFamilyId", SqlOperator.Equals, "ArticleFamilies.ArticleFamilyId");

            string result = "SELECT Articles.ArticleId,Articles.Name,ArticleFamilies.Name\n" +
                            "FROM [Articles] INNER JOIN [ArticleFamilies] ON ((Articles.ArticleFamilyId = ArticleFamilies.ArticleFamilyId))\n";

            Assert.AreEqual(result, sb.ToString());
            Select cloned = (Select) sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        public override void SimpleSelectAll()
        {
            Select sb = new Select()
                .Field("*")
                .From("Articles")
                .Where("Active = 1");

            string result = "SELECT *\n" +
                            "FROM [Articles]\n" +
                            "WHERE (Active = 1)\n";

            Assert.AreEqual(result, sb.ToString());

            Select cloned = (Select) sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        public override void SimpleSelectFields()
        {
            Select sb = new Select()
                .Field("ArticleId")
                .Field("Name")
                .From("Articles");

            string result = "SELECT ArticleId,Name\n" +
                            "FROM [Articles]\n";

            Assert.AreEqual(result, sb.ToString());
            Select cloned = (Select) sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }


        [Test]
        public override void SimpleDistinct()
        {
            Select sb = new Select()
                .Field("ArticleId")
                .Field("Name")
                .Distinct()
                .From("Articles");

            string result = "SELECT DISTINCT ArticleId,Name\n" +
                            "FROM [Articles]\n";

            Assert.AreEqual(result, sb.ToString());
            Select cloned = (Select) sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }


        [Test]
        public override void SelectFieldsWithFilter()
        {
            Select sb = new Select()
                .Field("ArticleId")
                .Field("Name")
                .From("Articles")
                .Where("Active = 1");

            string result = "SELECT ArticleId,Name\n" +
                            "FROM [Articles]\n" +
                            "WHERE (Active = 1)\n";

            Assert.AreEqual(result, sb.ToString());
            Select cloned = (Select) sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        public override void SelectFieldsWithFilterAndOperator()
        {
            Select sb = new Select()
                  .Field("ArticleId")
                  .Field("Name")
                  .From("Articles")
                  .Where("Active", SqlOperator.GreaterOrEqual, 0);

            string result = "SELECT ArticleId,Name\n" +
                            "FROM [Articles]\n" +
                            "WHERE (Active >= 0)\n";

            Assert.AreEqual(result, sb.ToString());
            Select cloned = (Select)sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());

        }


        [Test]
        public override void SimpleOrderBy()
        {
            Select sb = new Select("*")
                .From("Articles")
                .OrderBy("ArticleId DESC");

            string result = "SELECT *\n" +
                            "FROM [Articles]\n" +
                            "ORDER BY ArticleId DESC\n";

            Assert.AreEqual(result, sb.ToString());
            Select cloned = (Select) sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        public override void SimpleGroupBy()
        {
            Select sb = new Select()
                .Field("ArticleId")
                .Field("AVG(UnitCost)", "Coste medio por unidad")
                .From("Articles")
                .GroupBy("ArticleId");


            string result = "SELECT ArticleId,AVG(UnitCost) AS 'Coste medio por unidad'\n" +
                            "FROM [Articles]\n" +
                            "GROUP BY ArticleId\n";

            Assert.AreEqual(result, sb.ToString());
            Select cloned = (Select) sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }

        [Test]
        public override void SimpleHaving()
        {
            Select sb = new Select()
                .Field("ArticleId")
                .Field("AVG(UnitCost)", "Coste medio por unidad")
                .From("Articles")
                .GroupBy("ArticleId")
                .Having("Active = 1");

            string result = "SELECT ArticleId,AVG(UnitCost) AS 'Coste medio por unidad'\n" +
                            "FROM [Articles]\n" +
                            "GROUP BY ArticleId\n" +
                            "HAVING (Active = 1)\n";

            Assert.AreEqual(result, sb.ToString());
            Select cloned = (Select) sb.Clone();
            Assert.AreEqual(cloned.ToString(), sb.ToString());
        }
    }
}